package cn.iocoder.boot.service;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONException;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.*;

@Slf4j
public class ExcelToJsonArray {
    /**
     * 将Excel文件合并单元格并转换为JSON数组
     *
     * @param excelFilePath Excel文件路径
     * @param source_column Excel文件中的列名
     * @return JSON数组
     */
    public static JSONArray toJsonArray(String excelFilePath, String source_column) {
        String[] columns = source_column.split("\\|");
        // 使用流API去除空字符串
        String[] filteredColumns = Arrays.stream(columns)
                .filter(s -> s != null && !s.isEmpty())
                .map(String::trim)
                .toArray(String[]::new);
        Workbook workbook = getWorkbookByFilePath(excelFilePath);
        try {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            // 获取合并单元格信息
            Sheet mergedSheet = workbook.getSheetAt(0);
            CellRangeAddress[] mergedRegions = mergedSheet.getMergedRegions().toArray(new CellRangeAddress[0]);
            JSONArray jsonArray = new JSONArray();
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                // 校验最后一行为空行退出
                if (i == sheet.getLastRowNum()) {
                    boolean isBlankRow = true;
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (!"NaN".equals(getCellValue(cell))) {
                            isBlankRow = false;
                            break;
                        }
                    }
                    if (isBlankRow) break;
                }
                JSONObject jsonObject = new JSONObject();
                for (int j = 0; j < row.getLastCellNum(); j++) { //列遍历
                    Cell cell = row.getCell(j);
                    String cellValue = getCellValue2(cell);
                    if ("申请人".equals(cellValue)) {
                        return jsonArray;
                    }
                    for (CellRangeAddress mergedRegion : mergedRegions) {
                        if (mergedRegion.isInRange(row.getRowNum(), cell.getColumnIndex())) {
                            if (mergedRegion.getFirstRow() != mergedRegion.getLastRow()) {
                                // 取合并单元格的值
                                Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                                Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                                cellValue = getCellValue2(firstCell);
                            }
                            break;
                        }
                    }
                    if(cell.getColumnIndex() < filteredColumns.length) {
                        // 将单元格值添加到JsonObject中
                        jsonObject.set(filteredColumns[cell.getColumnIndex()], cellValue);
                    } else {
                        log.info("列索引超出范围，忽略该列的值");
                    }
                }
                jsonArray.add(jsonObject);
            }
            log.info("合并单元格后的数据={}", jsonArray.toStringPretty());
            // 去重逻辑：通过 HashSet 去重（需重写对象的 equals 和 hashCode）
            LinkedHashSet<Object> set = new LinkedHashSet<>(jsonArray);
            JSONArray deduplicatedArray = JSONUtil.parseArray(set);
            log.info("合并单元格并去重后的数据={}", deduplicatedArray.toStringPretty());
            return deduplicatedArray;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 删除非表头数据行
     *
     * @param json_source_data
     * @param source_column
     */
    public static void deletNotTitleRow(JSONArray json_source_data, String source_column) {
        int begin_row = findActualDataBeginRow(json_source_data, source_column);
        if (begin_row != -1) {
            // 删除前begin_row行
            for (int i = begin_row; i >= 0; i--) {
                json_source_data.remove(i);
            }
        }
    }

    /**
     * 行数据超出表头，则将超出的数据拼接到备注字段中
     * @param sourceDataObj
     * @param newData
     */
    public static void fillOutOfRangeData(JSONObject sourceDataObj, JSONObject newData) {
        String remark = newData.getStr("备注");
        for (String key : sourceDataObj.keySet()) {
            // 如果key是Unnamed开头的，则说明是行数据列超出表头，取出来拼接到一起
            if (key.startsWith("Unnamed")) {
                String value = sourceDataObj.getStr(key);
                if (value != null && !value.isEmpty() && !value.equals("NaN")) {
                    remark = remark + "," + value;
                }
            }
        }
        if (remark.startsWith(",")) {
            // 去掉第一个逗号
            remark = remark.substring(1);
        }
        newData.set("备注", remark);

//        for (int i = 0; i < dataList.size(); i++) {
//            JSONObject jsonObject = dataList.getJSONObject(i);
//            String remark = jsonObject.getStr("备注");
//            for (String key : jsonObject.keySet()) {
//                // 如果key是Unnamed开头的，则说明是行数据列超出表头，取出来拼接到一起
//                if (key.startsWith("Unnamed")) {
//                    String value = jsonObject.getStr(key);
//                    if (value != null && !value.isEmpty() && !value.equals("NaN")) {
//                        remark = remark + "," + value;
//                    }
//                }
//            }
//            if (remark.endsWith(",")) {
//                remark = remark.substring(0, remark.length() - 1);
//            }
//            jsonObject.set("备注", remark);
//        }
    }
    /**
     * 补充提供方字段
     *
     * @param source_data
     */
    public static void fillProviderFields(JSONArray source_data, JSONArray providerCfg) {
        // 客户已录入提供方，且为支持的
        List<String> validValues = Arrays.asList("不需要", "客供");
        // 遍历source_data，填充提供方字字段
        for (int i = 0; i < source_data.size(); i++) {
            JSONObject sourceDataObj = source_data.getJSONObject(i);
            String provideValue = sourceDataObj.getStr("提供方", "");
            sourceDataObj.set("提供方", provideValue.replaceAll("-",""));
            boolean findAndSet = false;
            for (int j = 0; j < providerCfg.size(); j++) {
                JSONObject providerCfjObj = providerCfg.getJSONObject(j);
                if (provideValue.equals(providerCfjObj.getStr("提供方值", ""))) {
                    String fieldNamesCfg = providerCfjObj.getStr("字段名", "");
                    String rowKeywordsCfg = providerCfjObj.getStr("行关键字匹配", "");
                    String finalValueCfg = providerCfjObj.getStr("匹配结果", "");
                    // 看是按字段匹配还是按行匹配
                    if (!fieldNamesCfg.equals("")) { // 按字段匹配
                        String[] fieldNameArr = fieldNamesCfg.split("\\|"); // 多字段名|隔开
                        for (String fieldName : fieldNameArr) {
                            String fieldValue = sourceDataObj.getStr(fieldName, "");
                            String [] words = providerCfjObj.getStr("列关键字匹配", "").split(",");
                            for(String word : words){
                                if(StrUtil.isBlank(word)){ continue; }
                                if (fieldValue.contains(word)) {
                                    sourceDataObj.set("提供方", finalValueCfg);
                                    findAndSet = true;
                                    break;
                                }
                            }
                        }
                    } else if (!rowKeywordsCfg.equals("")) {// 行关键字匹配
                        String rowKeywordArr[] = rowKeywordsCfg.split("\\|"); // 多关键字|隔开
                        for (String rowKeyword : rowKeywordArr) {
                            String[] words = rowKeyword.split("&");
                            if (words.length > 1) { // 多关键字需要同时满足
                                boolean allRight = true;
                                for(String word : words){
                                    if (!sourceDataObj.toStringPretty().contains(word)) {
                                        allRight = false;
                                        break;
                                    }
                                }
                                if(allRight){
                                    sourceDataObj.set("提供方", finalValueCfg);
                                    findAndSet = true;
                                    break;
                                }
                            } else {//单字关键字满足即可
                                if (sourceDataObj.toStringPretty().contains(words[0])) {
                                    sourceDataObj.set("提供方", finalValueCfg);
                                    findAndSet = true;
                                    break;
                                }
                            }
                            if (sourceDataObj.toStringPretty().contains(rowKeyword)) {
                                sourceDataObj.set("提供方", finalValueCfg);
                                findAndSet = true;
                                break;
                            }
                        }
                    } else { // 提供方匹配
                        sourceDataObj.set("提供方", finalValueCfg);
                        findAndSet = true;
                        break;
                    }
                }
            }
            if (StrUtil.isBlank(provideValue) && !findAndSet) {
                sourceDataObj.set("提供方", "金百泽"); // 默认值
            } else if(!StrUtil.isBlank(provideValue) && !validValues.contains(provideValue)) { // 已有值
                sourceDataObj.set("提供方", "金百泽"); // 默认值
            }
        }
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 解决数字类型单元格前面加了.0问题
     * @param cell
     * @return
     */
    private static String getCellValue2(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
//                    double numericValue = cell.getNumericCellValue();
//                    String value = String.valueOf(numericValue);
//                    if(value.contains("%")){
//                        return value;
//                    } else if (numericValue == Math.floor(numericValue)) {
//                        // 如果是整数，不带小数部分
//                        return String.valueOf((int) numericValue);
//                    } else {
//                        // 如果是小数，保留小数部分
//                        DecimalFormat df = new DecimalFormat("#.##");
//                        return df.format(numericValue);
//                    }
                    // 检查是否为百分比格式
                    CellStyle style = cell.getCellStyle();
                    if (style != null && style.getDataFormatString().contains("%")) {
                        double value = cell.getNumericCellValue();
                        DecimalFormat df = new DecimalFormat("#.##%"); // 自定义百分比格式
                        return df.format(value);
                    } else {
                        // 使用原有逻辑处理普通数值
                        double numericValue = cell.getNumericCellValue();
                        if (numericValue == Math.floor(numericValue)) {
                            return String.valueOf((int) numericValue);
                        } else {
                            DecimalFormat df = new DecimalFormat("#.##");
                            return df.format(numericValue);
                        }
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }


    /**
     * 判断是否有坚向合并单元格
     */
    public static boolean isMergedVertically(String excelFilePath, JSONArray md_source_data, String source_column) {
        Workbook workbook = getWorkbookByFilePath(excelFilePath);
        try {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            // 获取合并单元格信息
            Sheet mergedSheet = workbook.getSheetAt(0);
//            int numberOfMergedRegions = mergedSheet.getNumMergedRegions();
            CellRangeAddress[] mergedRegions = mergedSheet.getMergedRegions().toArray(new CellRangeAddress[0]);
//            int begin_row_no = -1;
            // 找出真正表头数据开始行
            String sourceColoumFormat = deleteSpecialChar(source_column);
            log.info("sourceColoumFormat:{}", sourceColoumFormat);
            boolean findHead = false;
            boolean findEndRowData = false;
            // 取md_source_data最后一行数据，判断结束行，避免非数据行的合并归为有合并单元格
            JSONObject endRowDataObj = md_source_data.getJSONObject(md_source_data.size() - 1);
            // 遍历 endRowDataObj
            String endRowData = "";
            for (String key : endRowDataObj.keySet()) {
                endRowData = endRowData + endRowDataObj.getStr(key);
            }
            endRowData = deleteSpecialChar(endRowData);
            log.info("endRowData:{}", endRowData);
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                String rowData = "";
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellValue = getCellValue2(cell);
                    rowData = rowData + cellValue;
                }
                rowData = deleteSpecialChar(rowData);
//                log.info("rowData:{}", rowData);
                if (rowData.equals(sourceColoumFormat)) {
                    findHead = true;
                    rowIterator.next();
                }
                if (rowData.equals(endRowData)) { //判断尾行
                    findEndRowData = true;
//                    rowIterator.next();
                }
                if (findHead && !findEndRowData) {
                    cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String cellValue = getCellValue(cell);
                        // 检查当前单元格是否是合并单元格的一部分
                        boolean isMergedVertically = false;
                        for (CellRangeAddress mergedRegion : mergedRegions) {
                            if (mergedRegion.isInRange(row.getRowNum(), cell.getColumnIndex())) {
                                if (mergedRegion.getFirstRow() != mergedRegion.getLastRow()) {
//                                    isMergedVertically = true;
//                                    break;
                                    return true;
                                }
                            }
                        }
//                        if (isMergedVertically) {
//                            return true;
//                        }
                    }
                }
            }

        } catch (Exception e) {
            log.error("isMergedVertically error", e);
        }
        return false;
    }

    /**
     * 兼容方式打开Excel文件
     *
     * @param excelFilePath
     * @return
     */
    public static Workbook getWorkbookByFilePath(String excelFilePath) {
        boolean forceOpenXlsx = false;
        Workbook workbook = null;
        try (FileInputStream inputStream = new FileInputStream(excelFilePath)) {
            if (excelFilePath.toLowerCase().endsWith(".xlsx") || excelFilePath.toLowerCase().endsWith(".xlsm")) {
                workbook = new XSSFWorkbook(inputStream); // 处理.xlsx 和 .xlsm 文件
            }  else if (excelFilePath.toLowerCase().endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream); // 处理.xls文件
            }
        } catch (IOException e) {
            // IO 异常，保留原始异常类型并添加上下文信息
            throw new RuntimeException("IO 错误: " + excelFilePath, e);
        } catch (OfficeXmlFileException e) {
            // 文件后缀与内容不匹配，比如后缀是xls，但文件内容是xlsx，则抛出异常
            forceOpenXlsx = true;
        }
        if (forceOpenXlsx) {
            try (FileInputStream inputStream = new FileInputStream(excelFilePath)) {
                workbook = new XSSFWorkbook(inputStream); // 处理.xlsx文件
            } catch (IOException e) {
                // IO 异常，保留原始异常类型并添加上下文信息
                throw new RuntimeException("IO 错误: " + excelFilePath, e);
            }
        }
        return workbook;
    }

    public static String deleteSpecialChar(String str) {
        String result = str.replaceAll("\\|", "").replaceAll(" ", "")
                .replaceAll("\\n", "").replaceAll("NaN", "");
        return result;
    }
    /**
     * 找json格式实际数据开始行
     *
     * @param json_source_data
     * @param source_column
     */
    public static int findActualDataBeginRow(JSONArray json_source_data, String source_column) {
        String sourceColoumFormat = deleteSpecialChar(source_column);
        log.info("sourceColoumFormat:{}", sourceColoumFormat);
        int begin_row = -1;
        // 遍历source_data，找到表头开始行
        for (int i = 0; i < json_source_data.size(); i++) {
            JSONObject jsonObject = json_source_data.getJSONObject(i);
            String title = "";
            // 遍历jsonObject，找到表头开始行
            for (String key : jsonObject.keySet()) {
                title = title + jsonObject.getStr(key);
            }
            title = deleteSpecialChar(title);
//            log.info("title:{}", title);
            if (title.equals(sourceColoumFormat)) {
                // 找到表头开始行，跳出循环
                begin_row = i;
                break;
            }
        }
        return begin_row;
    }

    /**
     * 制造商、制造商型号 替代料处理
     */
//    public static void manufacturerMerge(JSONArray source_data,String columns_relation) {
//        JSONObject columnsRelationObj = JSONUtil.parseObj(columns_relation);
//        String manufacturerFieldname = columnsRelationObj.getStr("制造商");
//        String manufacturerModelFieldname = columnsRelationObj.getStr("制造商型号");
//        String spec_fieldname = columnsRelationObj.getStr("规格描述");
//        if(!StrUtil.isBlank(manufacturerFieldname) && !StrUtil.isBlank(manufacturerModelFieldname)) { // 有映射到制造商、制造商型号列
//            JSONObject validObj = null;
//            for (int i = 0; i < source_data.size(); i++) {
//                JSONObject obj = source_data.getJSONObject(i);
//                String specValue = obj.getStr(spec_fieldname,"");
//                if(!"NaN".equals(specValue) || validObj == null) { // 记录有值的，用于下方逻辑判断
//                    validObj = obj;
//                } else { // 合并制造商、制造商规格
//                    String manufacturerValue = obj.getStr(manufacturerFieldname,"");
//                    String manufacturerOldValue = validObj.getStr(manufacturerFieldname,"");
//                    if (!"NaN".equals(manufacturerValue) && !manufacturerOldValue.contains(manufacturerValue)) {
//                        validObj.set(manufacturerModelFieldname, manufacturerOldValue + "/" + manufacturerValue);
//                    }
//                    String manufacturerModelValue = obj.getStr(manufacturerModelFieldname,"");
//                    String manufacturerModelOldValue = validObj.getStr(manufacturerModelFieldname,"");
//                    if (!"NaN".equals(manufacturerModelValue) && !manufacturerModelOldValue.contains(manufacturerModelValue)) {
//                        validObj.set(manufacturerModelFieldname, manufacturerModelOldValue + "/" + manufacturerModelValue);
//                    }
//                }
//            }
//            // 删除已合并行的无用行
//            for (int i = source_data.size() - 1; i > 0 ; i--) {
//                JSONObject obj = source_data.getJSONObject(i);
//                if(obj.getStr(spec_fieldname,"").equals("NaN")) {
//                    source_data.remove(i);
//                }
//            }
//        }
//
//    }
    public static void manufacturerMerge(JSONArray source_data,String columns_relation) {
        JSONObject columnsRelationObj = JSONUtil.parseObj(columns_relation);
        String manufacturerFieldname = columnsRelationObj.getStr("制造商");
        String manufacturerModelFieldname = columnsRelationObj.getStr("制造商型号");
        String spec_fieldname = columnsRelationObj.getStr("规格描述");
        if(!StrUtil.isBlank(manufacturerFieldname) && !StrUtil.isBlank(manufacturerModelFieldname)) { // 有映射到制造商、制造商型号列
            JSONObject tagObj = null;
            String currentSpecValue = "";
            for (int i = 0; i < source_data.size(); i++) {
                JSONObject obj = source_data.getJSONObject(i);
                String specValue = obj.getStr(spec_fieldname,"");
                if(!specValue.equals(currentSpecValue)) { // 开始新的合并
                    tagObj = obj;
                    currentSpecValue = specValue;
                }  else if(tagObj !=null)  {  // 合并制造商、制造商规格
                    String manufacturerValue = obj.getStr(manufacturerFieldname,"");
                    String manufacturerOldValue = tagObj.getStr(manufacturerFieldname,"");
                    if (!"NaN".equals(manufacturerValue) && !StrUtil.isBlank(manufacturerValue)) {
                        tagObj.set(manufacturerFieldname, manufacturerOldValue + "/" + manufacturerValue);
                        obj.set(spec_fieldname, "合并");
                    }
                    String manufacturerModelValue = obj.getStr(manufacturerModelFieldname,"");
                    String manufacturerModelOldValue = tagObj.getStr(manufacturerModelFieldname,"");
                    if (!"NaN".equals(manufacturerModelValue) && !StrUtil.isBlank(manufacturerModelValue)) {
                        tagObj.set(manufacturerModelFieldname, manufacturerModelOldValue + "/" + manufacturerModelValue);
                        obj.set(spec_fieldname, "合并");
                    }
                }
            }
            // 删除已合并行的无用行
            for (int i = source_data.size() - 1; i > 0 ; i--) {
                JSONObject obj = source_data.getJSONObject(i);
                if(obj.getStr(spec_fieldname,"").equals("合并")) {
                    source_data.remove(i);
                }
            }
        }

    }

    /**
     * 制造商、制造商型号（单独行未合并） 替代料处理
     * @param source_data
     * @param columns_relation
     */
    public static void manufacturerMerge2(JSONArray source_data,String columns_relation) {
        JSONObject columnsRelationObj = JSONUtil.parseObj(columns_relation);
        String manufacturerFieldname = columnsRelationObj.getStr("制造商");
        String manufacturerModelFieldname = columnsRelationObj.getStr("制造商型号");
        String spec_fieldname = columnsRelationObj.getStr("规格描述");
        if(!StrUtil.isBlank(manufacturerFieldname) && !StrUtil.isBlank(manufacturerModelFieldname)) { // 有映射到制造商、制造商型号列
            JSONObject tagObj = null;
            for (int i = 0; i < source_data.size(); i++) {
                JSONObject obj = source_data.getJSONObject(i);
                String specValue = obj.getStr(spec_fieldname,"");
                if(!"".equals(specValue) && !"NaN".equals(specValue)) { // 开始新的合并
                    tagObj = obj;
                }  else if(tagObj !=null)  {                  String manufacturerValue = obj.getStr(manufacturerFieldname,"");

                    String manufacturerOldValue = tagObj.getStr(manufacturerFieldname,"");
                    if (!"NaN".equals(manufacturerValue) && !StrUtil.isBlank(manufacturerValue)) {
                        tagObj.set(manufacturerFieldname, manufacturerOldValue + "/" + manufacturerValue);
                        obj.set(spec_fieldname, "合并");
                    }
                    String manufacturerModelValue = obj.getStr(manufacturerModelFieldname,"");
                    String manufacturerModelOldValue = tagObj.getStr(manufacturerModelFieldname,"");
                    if (!"NaN".equals(manufacturerModelValue) && !StrUtil.isBlank(manufacturerModelValue)) {
                        tagObj.set(manufacturerModelFieldname, manufacturerModelOldValue + "/" + manufacturerModelValue);
                        obj.set(spec_fieldname, "合并");
                    }
                }
            }
            // 删除已合并行的无用行
            for (int i = source_data.size() - 1; i > 0 ; i--) {
                JSONObject obj = source_data.getJSONObject(i);
                if(obj.getStr(spec_fieldname,"").equals("合并")) {
                    source_data.remove(i);
                }
            }
        }
    }
}
